\par \pard\plain \s31\sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1080\tx1440\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \f122\fs18 \tab put "UPPER(FIRSTNAME) = 'DAVID'" into queryString
\par \pard \s31\sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1080\tx1440\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \tab put DBQuery(queryString) into searchResult
\par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 Notice that the query string, like most normal st
rings, is bounded by double quotes and placed into the variable queryString. Again, the host environment only knows this as a string and does not parse the contents of the string.
But what if you wanted to replace the literal 'DAVID' with a string of your own choosing? Let's assume that you wanted to find the record matching the contents of the variable myFirstName. What many people do, and what WILL NOT WORK is the following:
\par \pard\plain \s31\sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1080\tx1440\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \f122\fs18 \tab -- the following won't work
\par \pard \s31\sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1080\tx1440\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \tab put "UPPER(FIRSTNAME) = 'myFirstName'" into queryString
\par \tab put DBQuery(queryString) into searchResult
All the above does is ask DBQuery to find a record where the contents of the field FIRSTNAME contains the literal string 'myFirstName'. What you need to do is construct a complex string. This is just pure Lingo or HyperTalk, there's no FileFlex magic here
. The easiest way to see this is to construct a string in the following way:
\par \pard\plain \s31\sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1080\tx1440\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \f122\fs18 \tab -- store the first half of the query expression string
\par \pard \s31\sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1080\tx1440\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \tab put "UPPER(FIRSTNAME) = '" into firstHalf
\par \tab -- build the string properly
\par \tab put firstHalf & myFirstName & "'" into queryString
\par \tab put DBQuery(queryString) into searchResult
Take extra care to notice that the single quote (') is contained in the double quotes and is passed to DBQuery. By making sure that the myFirstName variable is passed outside the double quotes, you're ensuring that it's evaluated by your host environment
Confusion evaluating strings has got to be one of the most common technical support calls. So read and study the descriptions
above, learn about strings in your host language, and please make sure you understand how strings work in your host language before calling us up. We'll just tell you to read this section anyway!
Once you have set up a search condition with DBLocate, it stays in effect until you invoke another one. DBSkip will follow the database sequence looking for records that match the criterion in the last DBLocate command. This chain is broken by use of the
\par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 FileFlex excels at ind
ex files. Letting FileFlex use indexes is like giving a prized racehorse the chance to just let it all out, throttling up a perfectly tuned race car, or punching a fighter jet into afterburner. When we say that FileFlex can locate any record in a sea of b
illions of records faster than the blink of an eye, we're talking FileFlex indexes.
Whenever possible, we recommend you use indexed-based searching. Indexes work by algorithm, rather than brute force. FileFlex looks at the string you're search for, does a
mathematical calculation that basically tells it how far into the file to move, and boom, it's on the record you need. By contrast, both DBQuery and the full-text search DBFindMemo scan on a record by record basis. This means that if the data you're looki
ng for is at the end of the file, DBQuery and DBFindMemo must individually check all the preceding records prior to finding the match. DBSeek (the interface to indexes) just does a calculation and whammo! You're on the record.
\par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 Indexes are not without their
price, however. Since indexes can't check every record and rely instead on complex offset calculations, they don't support complex queries. But they're ideal for most queries you'll need. Need to find someone's address? Construct an index combining last
name and first name, do a DBSeek, and--poof!--you're on the record. Need to find everyone in the Southeast Region who's booked over $1.25 million and who hasn't gotten a recent raise? Use DBQuery and be prepared to wait a while.
\par \pard \sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 Indexes also take disk spac
e, often quite a lot. Each index is it's own file and that file contains the complete data of the field it's indexing, as well as some overhead space used internally by FileFlex. So, if you've got this handy name and address database and you want to index
on last name, followed by first name, you've got one index file. If you want to index based on zipcode, you've got another index file, and so forth. But what the heck. CD-ROMs are big, text is small, and new hard drives are cheap. Use indexes and rejoice
It is possible to use DBSeek and DBQuery incorrectly and get dog-poor performance. These tools provide you with the capabilities. But it's up to you to design something that works efficiently. Just because you've got a hot database engine is no excuse fo
FileFlex supports the use and updating of dBASE III-compatible index files only. Other index file architectures such as FoxPro indexes cannot be used. However, FileFlex can reindex a
file using the dBASE index file structures. This makes it easy to use files which have been indexed using other methods.
Any time you have opened one or more index files related to an open database file and you make changes to that database file, FileFlex automatically updates those indexes to reflect the new file contents.
\par \pard\plain \s2\sb480\keepn\widctlpar \b\f8\fs36 {\*\bkmkstart _Toc349722617}{\*\bkmkstart _Toc349722840}{\*\bkmkstart _Toc349723129}{\*\bkmkstart _Toc349723276}Opening and Using Index Files{\*\bkmkend _Toc349722617}{\*\bkmkend _Toc349722840}
Use the FileFlex DBUseIndex function to open an index file for use. Supply the index file's name as an argument. Assign the result of this function to a variable (usually global) because you'll need to refer to the index file's ID in other scripts and han
dlers. Here's an example of the use of this function:
\par \pard\plain \s31\sl240\slmult0\widctlpar\tx-1440\tx-720\tx720\tx1080\tx1440\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\tx9360\tx10080\tx10800 \f122\fs18 \tab put DBUseIndex("STARS") into StarIndex
Note that the name of the index file should include any extension the file might have. In the Macintosh environment, extensions are normally omitted, but in DOS environments, the file extension ".NDX" will generally be used for dBASE III-compatible index
We regularly get tech support calls from customers who get index file errors. The most common reason is that the database the index file indexes must be open and selected (DBUse and DBSelect) before executing a DBUse. Oex indexID
DBCheckActive
return FileFlex("34", string(indexID))
end DBCheckIndex
on DBQuery expr
DBCheckActive
return FileFlex("35",expr)
end DBQuery
on DBLocate expr
-- NOTE: DBLocate is obsolete and will be eliminated in future releases
-- DBLocate has been replaced by DBQuery
DBCheckActive
return FileFlex("35",expr)
end DBLocate
on DBCurrDBNum
DBCheckActive
return FileFlex("36")
end DBCurrDBNum
on DBEncrypt theString, key
DBCheckActive
return FileFlex("38",theString, key)
end DBEncrypt
on DBDecrypt theString, key
DBCheckActive
return FileFlex( "39", theString, key)
end DBDecrypt
on DBFindMemo memoField, theString
DBCheckActive
return FileFlex("40",memoField, theString)
end DBFindMemo
on DBConvertCRLF theString, theOption
DBCheckActive
if the paramCount = 1 then
return FileFlex("41",theString)
else
return FileFlex("41",theString, theOption)
end if
end DBConvertCRLF
on DBPlatform
DBCheckActive
return FileFlex("42")
end DBPlatform
on DBTranslateChars string, table
DBCheckActive
return FileFlex("48",string, table)
end DBTranslateChars
on DBSetSortOrder order
global gDBWorldSort
global gDBSortOrder
if order = EMPTY then
put EMPTY into gDBWorldSort
else
put "1" into gDBWorldSort
put order into gDBSortOrder
end if
return 0
end DBSetSortOrder
on DBSetCaseTables upperTable, lowerTable
global gDBWorldCase
global gDBWorldUpper, gDBWorldLower
if (upperTable = EMPTY or lowerTable = EMPTY) then
put EMPTY into gDBWorldCase
else
put "1" into gDBWorldCase
put upperTable into gDBWorldUpper
put lowerTable into gDBWorldLower
end if
return 0
end DBSetCaseTables
on DBUpper s
DBCheckActive
return FileFlex("49",string(s))
end DBUpper
on DBLower s
DBCheckActive
return FileFlex("50",string(s))
end DBLower
on DBCheckActive
global gDBActive1030
if gDBActive1030 <> "true" then
put DBOpenSession() into dummy
end if
end DBCheckActive
-- These routines should never be called by the user. They simply
-- assign and gather values of the global variables. They are here so
-- we can test the global value interface to the host application